﻿/*-----------------------------------------------------------------------
	Copyright (C) 2008 合肥工业大学
	文件名：ChinaAnalysis.cs
	创建者：林杰
	创建时间：05/02/2008 21:35:19
	描述：  中国专项分析类         
 -----------------------------------------------------------------------*/
namespace Patent.CN
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using Patent.DBUtility;

    #region 原产地类型
    /// <summary>
    /// 原产地类型
    /// </summary>
    public enum OriginPlaceType
    {
        /// <summary>
        /// 国家
        /// </summary>
        Country,//0
        /// <summary>
        /// 省份
        /// </summary>
        Province//1

    }
    #endregion

    /// <summary>
    /// ChinaAnalysis 的摘要说明
    /// </summary>
    public class ChinaAnalysis
    {
        #region 属性
        private int m_topicID;
        /// <summary>
        /// 专题号
        /// </summary>
        public int TopicID
        {
            get { return m_topicID; }
            set { m_topicID = value; }
        }
        private string m_topicName;
        /// <summary>
        /// 专题名称
        /// </summary>
        public string TopicName
        {
            get
            {
                if (string.IsNullOrEmpty(m_topicName))
                {
                    GetTopicInfo();
                }
                return m_topicName;
            }
        }
        private string m_topicTable;
        /// <summary>
        /// 信息表名
        /// </summary>
        public string TopicTable
        {
            get { return m_topicTable; }
        }
        /// <summary>
        /// 数据源
        /// </summary>
        private string m_topicSource;

        private string  m_originPlaceTable;
        /// <summary>
        /// 原产区表
        /// </summary>
        public string  OriginPlaceTable
        {
            get { return m_originPlaceTable; }
            set { m_originPlaceTable = value; }
        }

        public string M_topicTable
        {
            set { m_topicTable = value; }
            get { return m_topicTable; }
        }



        #endregion
        public ChinaAnalysis()
        {
        }

        public ChinaAnalysis(int topicID)
        {
            m_topicID = topicID;
            GetTopicInfo();
        }

        /// <summary>
        /// 判断是否是中国专利
        /// </summary>
        /// <returns></returns>
        public bool IsChineseTopic()
        {
            bool ret = false;
            if (string.IsNullOrEmpty(m_topicSource))
            {
                GetTopicInfo();
            }
            if (string.IsNullOrEmpty(m_topicSource))
            {
                throw new FormatException("数据源字段为空！");
            }
            else
            {
                // 第8 9 10代表中国发明专利 中国实用新型专利 中国外观设计
                if (m_topicSource[8] == '1' || m_topicSource[9] == '1' || m_topicSource[10] == '1')
                {
                    ret = true;
                }
                else
                {
                    ret = false;
                }
            }

            return ret;
        }

        /// <summary>
        /// 获取专题信息
        /// </summary>
        /// <returns></returns>
        public bool GetTopicInfo()
        {
            try
            {
                if (m_topicID < 0)
                {

                    throw new FormatException();
                }
                string sql = "select 专题名称,信息表名,原产区表,数据源 from ptcase_m where 专题编号=@topicID";
                Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
                SqlParameter[] parms = new SqlParameter[1];
                parms[0] = helper.CreateInParam("@topicID", SqlDbType.Int, 0, m_topicID);
                SqlDataReader reader;
                helper.RunSQL(sql, parms, out reader);
                if (reader.HasRows)
                {
                    reader.Read();
                    m_topicName = reader.IsDBNull(0) ? "" : reader.GetString(0);
                    m_topicTable = reader.IsDBNull(1) ? "" : reader.GetString(1);
                    m_originPlaceTable = reader.IsDBNull(2) ? "" : reader.GetString(2);
                    m_topicSource = reader.IsDBNull(3) ? "" : reader.GetString(3);
                }
                else
                {
                    throw new NullReferenceException();
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;



        }
        /// <summary>
        /// 获取专利统计总数
        /// </summary>
        /// <param name="type">专利类型:syxx,wgzl,fmzl</param>
        /// <returns></returns>
        public int GetCount(string type)
        {
            int ret = 0;
            if (type != "syxx" && type != "wgzl" && type != "fmzl")
            {
                return -1;
            }
            string sql = "select 记录总数 from ptprocess where 专题信息表=@topicTable and 数据表名=@type";
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[2];
            parms[0] = helper.CreateInParam("@topicTable", SqlDbType.VarChar, 20, m_topicTable);
            parms[1] = helper.CreateInParam("@type", SqlDbType.VarChar, 10, type);
            SqlDataReader reader;
            helper.RunSQL(sql, parms, out reader);
            //实用新型
            if (reader != null && reader.HasRows)
            {
                reader.Read();
                ret = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader.GetValue(0));
            }
            else
            {
                ret = 0;
            }
            helper.Close();
            return ret;
        }
        /// <summary>
        /// 获取原产地名称列表
        /// </summary>
        /// <param name="type">原产地类型</param>
        /// <returns></returns>
        public List<string> GetOriginPlaceList(OriginPlaceType type)
        {
            if (string.IsNullOrEmpty(m_topicTable))
            {
                throw new FormatException("信息表名为空");
            }
            string sql=string.Empty;
            if (type == OriginPlaceType.Country)
            {
                sql = "select 国省代码,总计 from "+m_originPlaceTable+" where 国省类型=0";
            }
            if (type == OriginPlaceType.Province)
            {
                sql = "select 国省代码,总计 from " + m_originPlaceTable + " where 国省类型=1";
            }

            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = helper.CreateInParam("@InfoTable", SqlDbType.VarChar, 10, m_topicTable);
            DataSet ds = new DataSet();
            helper.RunSQL(sql, parms, ref ds);
            List<string> itemsList = new List<string>();
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    itemsList.Add(item[0].ToString()+"("+item[1].ToString()+")");
                }
            }
            helper.Close();
            return itemsList;
        }



        public DataTable GetOriginPlaceTable(OriginPlaceType type)
        {
            if (string.IsNullOrEmpty(m_topicTable))
            {
                throw new FormatException("信息表名为空");
            }
            string sql = string.Empty;
            if (type == OriginPlaceType.Country)
            {
                sql = "select 国省代码,总计,round(CAST(总计*100 AS float)/(select sum(记录总数) from ptprocess where 专题信息表=@InfoTable),2)  as 百分比  from " + m_originPlaceTable + " where 国省类型=0 order by 总计 desc";
            }
            if (type == OriginPlaceType.Province)
            {
                sql = "select 国省代码,总计,round(CAST(总计*100 AS float)/(select sum(记录总数) from ptprocess where 专题信息表=@InfoTable),2) as 百分比  from " + m_originPlaceTable + " where 国省类型=1 order by 总计 desc";
            }
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = helper.CreateInParam("@InfoTable", SqlDbType.VarChar, 20, m_topicTable);
            DataSet ds = new DataSet();
            helper.RunSQL(sql, parms, ref ds);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            helper.Close();
            return null;

        }

        public int GetChinesePatentCount()
        {
            int ret = -1;
            string sql = "select sum(记录总数) as 数量 from ( select 记录总数 from ptprocess where 数据表名='wgzl' and 专题信息表=@TopicTable union all select 记录总数 from ptprocess where 数据表名='syxx' and 专题信息表=@TopicTable union all select 记录总数 from ptprocess where 数据表名='fmzl' and 专题信息表=@TopicTable )  as testfoo ";
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[1];
            if (string.IsNullOrEmpty(m_topicTable))
            {
                throw new Exception("GetTopicCount函数中参数信息表名为空");
            }
            parms[0] = helper.CreateInParam("@topicTable", SqlDbType.VarChar, 20, m_topicTable);
            SqlDataReader reader;
            helper.RunSQL(sql, parms, out reader);
            if (reader != null && reader.HasRows)
            {
                reader.Read();
                ret = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader.GetValue(0));
            }
            else
            {
                ret = -1;
            }
            helper.Close();
            return ret;

        }

        public DataTable GetAreaTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("类型", typeof(string)));
            dt.Columns.Add(new DataColumn("数量", typeof(int)));
            dt.Columns.Add(new DataColumn("百分比", typeof(string)));


            DataRow dtRow = dt.NewRow();
            int chinese = GetChinesePatentCount();
            int all = GetAllPatentsCount();
            if (all == -1)
            {
                all = 0;
            }
            if (chinese == -1)
            {
                dtRow["类型"] = "国内";
                dtRow["数量"] = 0;
                dtRow["百分比"] = "0%";
            }
            else
            {
                dtRow["类型"] = "国内";
                dtRow["数量"] = chinese;
                dtRow["百分比"] = (float)chinese / all * 100 + "%";
            }


            DataRow dr2 = dt.NewRow();
            dr2["类型"] = "国外";
            dr2["数量"] = all - chinese;
            dr2["百分比"] = (1 - (float)chinese / all) * 100 + "%";

            DataRow dr3 = dt.NewRow();
            dr3["类型"] = "总计";
            dr3["数量"] = all;
            dr3["百分比"] = "100%";
            dt.Rows.Add(dtRow);
            dt.Rows.Add(dr2);
            dt.Rows.Add(dr3);

            return dt;

        }
        /// <summary>
        /// 获取原产区统计信息表
        /// </summary>
        /// <param name="places"></param>
        /// <returns></returns>
        public DataTable GetOriginPlaceResult(List<string> places)
        {
            string sql="select 国省代码,发明专利,实用新型,外观设计,总计 from "+m_originPlaceTable;
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql,  ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                
                dt.Columns.Add(new DataColumn("国省代码", typeof(string)));
                dt.Columns.Add(new DataColumn("发明专利", typeof(int)));
                dt.Columns.Add(new DataColumn("实用新型", typeof(int)));
                dt.Columns.Add(new DataColumn("外观设计", typeof(int)));
                dt.Columns.Add(new DataColumn("总计", typeof(int)));
                ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns[0] };
                foreach(string place in places)
                {

                    DataRow dr=ds.Tables[0].Rows.Find(place);
                    DataRow drfoo = dt.NewRow();
                    drfoo["国省代码"] = dr[0];
                    drfoo["发明专利"] = dr[1];
                    drfoo[2] = dr[2];
                    drfoo[3] = dr[3];
                    drfoo[4] = dr[4];
                    dt.Rows.Add(drfoo);
                }
            }
            helper.Close();
            return dt;
        }
        /// <summary>
        /// 趋势分析
        /// </summary>
        /// <returns></returns>
        public DataTable GetTrendTable()
        {
            string sql = "select 年份,sum(实用新型) as 实用新型,sum(发明专利) as 发明专利,sum(外观设计) as 外观设计, sum(实用新型)+sum(发明专利)+sum(外观设计) as 总计 from ( select left(公开日,4) as 年份,count(*) as 实用新型 ,0 as 发明专利, 0 as 外观设计 from " + m_topicTable + " where 专利组织='syxx' group by left(公开日,4) union all select left(公开日,4) as 年份,0 as 实用新型,count(*) as 发明专利  , 0 as 外观设计 from " + m_topicTable + " where 专利组织='fmzl' group by left(公开日,4) union all select left(公开日,4) as 年份 ,  0 as 实用新型 ,0 as 发明专利,count(*) as 外观设计 from " + m_topicTable + " where 专利组织='wgzl' group by left(公开日,4) ) as  testfoo group by 年份 ";
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql,  ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();
            return dt;

        }
        /// <summary>
        ///  获取专题中专利条数
        /// </summary>
        /// <returns></returns>
        public int GetAllPatentsCount()
        {
            int ret = -1;
            string sql = "select sum(记录总数) from ptprocess where 专题信息表=@topicTable";
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[1];
            if(string.IsNullOrEmpty(m_topicTable))
            {
                throw new Exception("GetTopicCount函数中参数信息表名为空");
            }
            parms[0] = helper.CreateInParam("@topicTable", SqlDbType.VarChar, 20, m_topicTable);
            SqlDataReader reader;
            helper.RunSQL(sql, parms, out reader);
            
            if (reader != null && reader.HasRows)
            {
                reader.Read();
                ret = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader.GetValue(0));
            }
            else
            {
                ret = 0;
            }
            helper.Close();
            return ret;

        }

        public DataTable GetApplySumTable(int count)
        {
            string sql = "select top " + count + " 申请人,sum(实用新型) as 实用新型,sum(发明专利) as 发明专利,sum(外观设计) as 外观设计, sum(实用新型)+sum(发明专利)+sum(外观设计) as 总计 from ( select 申请人 as 申请人,count(*) as 实用新型 ,0 as 发明专利, 0 as 外观设计 from " + m_topicTable + " where 专利组织='syxx' group by 申请人 union all select 申请人 as 申请人,0 as 实用新型,count(*) as 发明专利  , 0 as 外观设计 from " + m_topicTable + " where 专利组织='fmzl' group by 申请人 union all select 申请人 as 申请人 ,  0 as 实用新型 ,0 as 发明专利,count(*) as 外观设计 from " + m_topicTable + " where 专利组织='wgzl' group by 申请人 ) as  testfoo group by 申请人 order by 总计 desc ";
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql, ref ds);
            
            if (ds != null && ds.Tables.Count > 0)
            {
                helper.Close();
                ds.Tables[0].Columns.Add("标志");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ds.Tables[0].Rows[i]["标志"] = tenToTS(i);
                }
                    return ds.Tables[0];
            }
            helper.Close();
            return null;
            
        }

        /// <summary>
        /// 获取申请人数量统计
        /// </summary>
        /// <param name="applicantNames">申请人姓名</param>
        /// <returns>数据表</returns>
        public DataTable GetApplicantSumTable(string[] applicantNames)
        {
            if (applicantNames.Length == 0)
            {
                return null;
            }

            // 拼接申请人姓名
            string sqlConditions = string.Empty;
            foreach (string applicantName in applicantNames)
            {
                if(!string.IsNullOrEmpty(applicantName))
                {
                    sqlConditions += "'" + applicantName + "',";
                }
            }

            // 删除最后的逗号
            sqlConditions = sqlConditions.Substring(0, sqlConditions.Length - 1);

            string sql = "select 申请人,sum(实用新型) as 实用新型,sum(发明专利) as 发明专利,sum(外观设计) as 外观设计, sum(实用新型)+sum(发明专利)+sum(外观设计) as 总计 from ( select 申请人 as 申请人,count(*) as 实用新型 ,0 as 发明专利, 0 as 外观设计 from " + m_topicTable + " where 专利组织='syxx' group by 申请人 union all select 申请人 as 申请人,0 as 实用新型,count(*) as 发明专利  , 0 as 外观设计 from " + m_topicTable + " where 专利组织='fmzl' group by 申请人 union all select 申请人 as 申请人 ,  0 as 实用新型 ,0 as 发明专利,count(*) as 外观设计 from " + m_topicTable + " where 专利组织='wgzl' group by 申请人 ) as  testfoo where 申请人 in (" +sqlConditions +") group by 申请人 order by 总计 desc ";

            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();

            helper.RunSQL(sql, ref ds);

            if (ds != null && ds.Tables.Count > 0)
            {
                helper.Close();
                ds.Tables[0].Columns.Add("标志");

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ds.Tables[0].Rows[i]["标志"] = tenToTS(i);
                }

                return ds.Tables[0];
            }

            helper.Close();
            return null;
        }
        /// <summary>
        /// 将十进制数换算成用二十六位大些字母表示的字符串
        /// </summary>
        /// <param name="num">十进制数</param>
        /// <returns>字符串（用二十六位大些字母表示）</returns>
        public string tenToTS(int num)
        {
            string[] digit ={ "A", "B", "C", "D", "E", "F", "G",
                              "H", "I", "J", "K", "L", "M", "N", 
                              "O", "P", "Q", "R", "S", "T",
                              "U", "V", "W", "X", "Y", "Z"};
            if (num >= 0 && num < 26)
                return digit[num];
            else
            {
                return digit[num / 26] + tenToTS(num % 26);
            }
        }

        public DataTable GetApplicantNames(string keyword)
        {
            string sql = "select distinct top 10 申请人 from " + m_topicTable + " where 申请人 like '" + keyword + "%'";
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql, ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();
            
            return dt;

        }


        public DataTable GetApplicantTrand(string topicID,string tableName,string applicant)
        {
            //string sql = "select 年份,sum(实用新型) as 实用新型,sum(发明专利) as 发明专利,sum(外观设计) as 外观设计, sum(实用新型)+sum(发明专利)+sum(外观设计) as 总计 from ( select left(申请日,4) as 年份,count(*) as 实用新型 ,0 as 发明专利, 0 as 外观设计 from " + m_topicTable + " where 专利组织='syxx' and 申请人=@Applicant group by left(申请日,4) union all select left(申请日,4) as 年份,0 as 实用新型,count(*) as 发明专利  , 0 as 外观设计 from " + m_topicTable + " where 专利组织='fmzl' and 申请人=@Applicant group by left(申请日,4) union all select left(申请日,4) as 年份 ,  0 as 实用新型 ,0 as 发明专利,count(*) as 外观设计 from " + m_topicTable + "  where 专利组织='wgzl' and 申请人=@Applicant group by left(申请日,4) ) as  testfoo group by 年份 order by 年份 ";
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[3];
            parms[0] = helper.CreateInParam("@name", SqlDbType.VarChar, 200, applicant);
            parms[1] = helper.CreateInParam("@tableName", SqlDbType.VarChar, 100, tableName);
            parms[2] = helper.CreateInParam("@topicID", SqlDbType.Int, 0, topicID);

            helper.RunProc("getApplicantTrand", parms, ref ds);
            
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count>0)
            {                
                helper.Close();
                return ds.Tables[0];
            }
            helper.Close();

            return null;
        }

        public DataTable GetAllApplicantNames()
        {
            string sql = "select 申请人,count(*) as 数量 from " + m_topicTable + " group by 申请人 order by 数量 desc";
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql, ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();

            return dt;
        }

        /// <summary>
        /// 获取发明专利信息表
        /// </summary>
        /// <param name="start">从第几条起</param>
        /// <param name="limit">共几条</param>
        /// <returns></returns>
        public DataTable GetPatentInfoTable(string type,int start, int limit)
        {
            //start += 1; //ext从0开始,sqlserver从1开始,  但使用sql2000时不需要加
            int end=start +limit-1;
            // sql2000 is not support ROW_NUMBER() function
            //string sql = "SELECT  申请号,公开日,公开号,申请日,专利号,颁证日,申请人,主分类号,名称,国省代码 FROM (select 申请号,公开日,公开号,申请日,专利号,颁证日,申请人,主分类号,名称,国省代码,ROW_NUMBER() Over(order by 申请号) as rowNum from "            + m_topicTable + " where 专利类型='" + type + "' ) as myTable where rowNum between " + start.ToString() + " and " + end.ToString();

            string sql = string.Format("select top {0} 申请号,公开日,公开号,申请日,专利号,颁证日,申请人,主分类号,名称,国省代码 from  {1}  where 专利类型='{2}' and 申请号 not in (select top {3} 申请号 from {1}  where 专利类型='{2}' order by 申请号)  order by 申请号", limit, m_topicTable, type, start);

            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            DataSet ds = new DataSet();
            helper.RunSQL(sql, ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();

            return dt;
        }

        public DataTable GetApplicantTrendTree(string applicant)
        {
            string count = "10";
            SQLHelper helper = new SQLHelper();
            DataSet ds = new DataSet();
            
            string sql = "select top " + count + " 申请人,sum(实用新型) as 实用新型,sum(发明专利) as 发明专利,sum(外观设计) as 外观设计, sum(实用新型)+sum(发明专利)+sum(外观设计) as 总计 from ( select 申请人 as 申请人,count(*) as 实用新型 ,0 as 发明专利, 0 as 外观设计 from " + m_topicTable + " where 专利组织='syxx' and 申请人 like '%" + applicant + "%' group by 申请人 union all select 申请人 as 申请人,0 as 实用新型,count(*) as 发明专利  , 0 as 外观设计 from " + m_topicTable + " where 专利组织='fmzl' and 申请人 like '%" + applicant + "%' group by 申请人 union all select 申请人 as 申请人 ,  0 as 实用新型 ,0 as 发明专利,count(*) as 外观设计 from " + m_topicTable + " where 专利组织='wgzl' and 申请人 like '%" + applicant + "%' group by 申请人 ) as  testfoo group by 申请人 order by 总计 desc ";
            helper.RunSQL(sql, ref ds);

            if (ds != null && ds.Tables.Count > 0)
            {
                helper.Close();
                return ds.Tables[0];
            }
            helper.Close();
            return null;
        }

        /// <summary>
        ///  获取原产区分析表
        /// </summary>
        /// <param name="area">国省代码</param>
        /// <param name="localTable">国省代码表</param>
        /// <returns></returns>
        public DataTable GetLocalArea(string[] area, string localTable)
        {
            string condition = "";
            for (int i = 0; i < area.Length; i++)
            {
                if (string.IsNullOrEmpty(condition))
                {
                    condition = " [国省代码]='" + area[i] + "' ";
                }
                else
                {
                    condition = condition + " or [国省代码]='" + area[i] + "' ";
                }
            }

            string account = Convert.ToString(GetLocalAreaAccount(localTable));
            SQLHelper helper = new SQLHelper();
            DataSet data = new DataSet();
            string sqlsq = "SELECT [国省代码] as dm,[名称] as mc,[实用新型] as sy,[发明专利] as fm,[外观设计] as wg,[总计] as zj," + SetPer("[实用新型]", account, "syper") + "," + SetPer("[发明专利]", account, "fmper") + "," + SetPer("[外观设计]", account, "wgper") + "," + SetPer("[总计]", account, "zjper") + " FROM " + localTable + ",dm_sq where  ([代码]=[国省代码]) and (" + condition + ")";
            string sqlgj = "SELECT [国省代码] as dm,[名称] as mc,[实用新型] as sy,[发明专利] as fm,[外观设计] as wg,[总计] as zj," + SetPer("[实用新型]", account, "syper") + "," + SetPer("[发明专利]", account, "fmper") + "," + SetPer("[外观设计]", account, "wgper") + "," + SetPer("[总计]", account, "zjper") + " FROM " + localTable + ",dm_gj where  ([代码]=[国省代码]) and (" + condition + ")"; ;

            string sql = sqlsq + " union " + sqlgj;
            helper.RunSQL(sql, ref data);
            return data.Tables[0];
        }


        /// <summary>
        /// 百分比
        /// </summary>
        /// <param name="fz">分子</param>
        /// <param name="fm">分母</param>
        /// <param name="name">百分率名称</param>
        /// <returns></returns>
        public string SetPer(string fz, string fm, string name)
        {
            return " cast(cast(" + fz + "/cast(" + fm + " as float)*100 as decimal(18,2)) as varchar)+'%' as " + name + " ";
        }

        /// <summary>
        /// 求国省代码的总和， 
        /// </summary>
        /// <param name="localTabel"></param>
        /// <returns></returns>
        /// <remarks>Accout 怀疑为Amount的笔误</remarks>
        public int GetLocalAreaAccount(string localTabel)
        {
            int account = 0;
            SQLHelper helper = new SQLHelper();
            DataSet data = new DataSet();
            string sql = "select sum(总计) as 总和 from " + localTabel + " where (国省代码>='00' and 国省代码<='99' and Len([国省代码])=2) or (国省代码>='AA' and 国省代码<='ZZ' and Len([国省代码])=2)";
            helper.RunSQL(sql, ref data);
            if (data.Tables[0].Rows.Count > 0)
            {
                account = Convert.ToInt32(data.Tables[0].Rows[0]["总和"].ToString());
            }
            return account;
        }

        /// <summary>
        /// 获取发明专利的申请人姓名
        /// </summary>
        /// <returns></returns>
        public DataTable GetNameOfFMZLApplicants()
        {
            string sql = "select distinct 申请人 from " + m_topicTable;
            DataSet ds = new DataSet();
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            helper.RunSQL(sql, ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();
            
            return dt;
        }

        /// <summary>
        /// 获取申请人姓名
        /// </summary>
        /// <param name="start">从第几条起</param>
        /// <param name="limit">共几条</param>
        /// <returns></returns>
        public DataTable GetNameOfApplicants(int start, int limit)
        {
            // sql 2000 is not support ROW_NUMBER() function
            // start += 1; //ext从0开始,sqlserver从1开始,  但使用sql2000时不需要加
            //int end = start + limit - 1;
            //string sql = "select 申请人 from (select  申请人 ,ROW_NUMBER() Over(order by 申请人) as rowNum from (select distinct 申请人 from " + m_topicTable + " ) as applicantTable ) as mytable where rowNum between @start and @end";

            string sql = string.Format("select distinct top {2} 申请人 from {0}  where 申请人 not in (select distinct top {1} 申请人 from {0} order by 申请人)order by 申请人", m_topicTable, start.ToString(), limit.ToString());

            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            DataSet ds = new DataSet();
           
            helper.RunSQL(sql, ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();

            return dt;

        }
        
        /// <summary>
        /// 获取申请人名称表
        /// </summary>
        /// <param name="start">从第几条开始</param>
        /// <param name="limit">共几条</param>
        /// <param name="queryWord">名称关键字</param>
        /// <returns>申请人名称表</returns>
        public DataTable GetNameOfApplicants(int start, int limit, string queryWord)
        {
            if (string.IsNullOrEmpty(queryWord))
            {
                return GetNameOfApplicants(start, limit);
            }
            queryWord = "%" + queryWord + "%";

            //start += 1; //ext从0开始,sqlserver从1开始,but sql2000 is not need.
            //int end = start + limit - 1;
            //string sql = "select 申请人 from (select  申请人 ,ROW_NUMBER() Over(order by 申请人) as rowNum from (select distinct 申请人 from " + m_topicTable + " where 申请人 like @queryWord ) as applicantTable ) as mytable where rowNum between @start and @end";
            //SqlParameter[] parms = new SqlParameter[3];
            //parms[0] = helper.CreateInParam("@queryWord", SqlDbType.VarChar, 50, queryWord);
            //parms[1] = helper.CreateInParam("@start", SqlDbType.Int, 0, start);
            //parms[2] = helper.CreateInParam("@limit", SqlDbType.Int, 0, limit);

            string sql = string.Format("select distinct top {2} 申请人 from {0}  where 申请人 like  '{3}'  and 申请人 not in (select distinct top {1} 申请人 from {0} where 申请人 like '{3}'  order by 申请人)order by 申请人", m_topicTable, start.ToString(), limit.ToString(), queryWord);

            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            DataSet ds = new DataSet();

            helper.RunSQL(sql,  ref ds);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            helper.Close();

            return dt;

        }

        /// <summary>
        /// 获取申请人名称统计
        /// </summary>
        /// <returns>数量</returns>
        public int GetNameOfApplicantsCount()
        {
            int ret = 0;
            string sql = "select count(distinct 申请人) from " + m_topicTable;
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlDataReader reader;
                helper.RunSQL(sql, out reader);
            if (reader != null && reader.HasRows)
            {
                reader.Read();
                ret = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader.GetValue(0));
            }
            else
            {
                ret = -1;
            }
            helper.Close();
            return ret;
        }

        /// <summary>
        /// 获取申请人名称统计
        /// </summary>
        /// <param name="queryWord">查询关键字</param>
        /// <returns>数量</returns>
        public int GetNameOfApplicantsCount(string queryWord)
        {
            if (string.IsNullOrEmpty(queryWord))
            {
                return GetNameOfApplicantsCount();
            }
            queryWord = "%" + queryWord + "%";
            int ret = 0;
            string sql = "select count(distinct 申请人) from " + m_topicTable 
                + " where 申请人 like @queryWord";
            Patent.DBUtility.SQLHelper helper = new Patent.DBUtility.SQLHelper();
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = helper.CreateInParam("@queryWord", SqlDbType.VarChar, 50, queryWord);
            SqlDataReader reader;
            helper.RunSQL(sql, parms, out reader);
            if (reader != null && reader.HasRows)
            {
                reader.Read();
                ret = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader.GetValue(0));
            }
            else
            {
                ret = -1;
            }
            helper.Close();
            return ret;
        }
    }
}
